import MySQLdb

# 连接数据库
def connect():
    db = MySQLdb.connect(
        host='localhost',
        port=3306,
        user='root',
        db='test3',
        passwd='',
        charset='utf8'
    )
    return db

# 插入数据函数
def insert():
    print('''
    请选择插入的表：
    1. S
    2. SPJ
    0. 返回
    ''')
    print('> ', end='')
    table_num = input()
    if table_num == '1':
        table = 'S'
        print('''
    请输入供应商名称、供应商状态、供应商所在城市，用空格隔开。
    例如：精益 20 天津
        ''')
        print('> ', end='')
        table_S = input().split(" ")
        db = connect()
        cursor = db.cursor()
        try:
            cursor.execute("SELECT SNO FROM S")
            SNO_list = cursor.fetchall()
            Max_No = max([int(i[0][1:]) for i in SNO_list])
        except Exception as e:
            if(str(e) == 'max() arg is an empty sequence'):
                Max_No = 0
            else:
                print("\n查询最大SNO失败！错误信息："+str(e)+"\n")
                db.close()
                return
        try:
            cursor.execute("INSERT INTO S VALUES ('{}', '{}', {}, '{}');".format("S"+str(Max_No+1), table_S[0], table_S[1], table_S[2]))
            db.commit()
            print("\n插入成功！\n({}, {}, {}, {})\n".format("S"+str(Max_No+1), table_S[0], table_S[1], table_S[2]))
        except Exception as e:
            db.rollback()
            print("\n插入失败！错误信息："+str(e)+"\n")
        db.close()
    elif table_num == '2':
        table = 'SPJ'
        print('''
    请输入供应商代码、零件代码、工程项目代码、供应数量，用空格隔开。
    例如：S1 P1 J1 200
        ''')
        print('> ', end='')
        table_SPJ = input().split(" ")
        db = connect()
        cursor = db.cursor()
        try:
            cursor.execute("INSERT INTO SPJ VALUES ('{}', '{}', '{}', {});".format(table_SPJ[0],table_SPJ[1],table_SPJ[2],table_SPJ[3]))
            db.commit()
        except Exception as e:
            db.rollback()
            print("\n插入失败！错误信息："+str(e)+"\n")
        db.close()
    elif table_num == '0':
        return
    else:
        print('输入错误！请重新输入！\n')
        insert()
        return


# 查询数据函数
def search():
    print('''
    请选择查询方式：
    1. 通过供应商代码查询
    2. 通过供应商状态查询
    0. 返回
    ''')
    print('> ', end='')
    search_num = input()
    if search_num == '1':
        print('''
    请输入供应商代码：
    例如：S1
        ''')
        print('> ', end='')
        SNO = input()
        db = connect()
        cursor = db.cursor()
        try:
            cursor.execute("SELECT * FROM S WHERE SNO = '{}';".format(SNO))
            result = cursor.fetchall()
            print("\n查询结果：\n{}\n".format(result))
        except Exception as e:
            print("\n查询失败！错误信息："+str(e)+"\n")
        db.close()
    elif search_num == '2':
        print('''
    请输入供应商状态：
    例如：20
        ''')
        print('> ', end='')
        SSTATUS = input()
        db = connect()
        cursor = db.cursor()
        try:
            cursor.execute('SELECT * FROM S WHERE STATUS = {};'.format(SSTATUS))
            result = cursor.fetchall()
            print("\n查询结果：\n{}\n".format(result))
        except Exception as e:
            print("\n查询失败！错误信息："+str(e)+"\n")
        db.close()
    elif search_num == '0':
        return
    else:
        print('输入错误！请重新输入！\n')
        search()
        return

# 删除数据函数
def delete():
    db = connect()
    cursor = db.cursor()
    try:
        cursor.execute('SELECT SNO FROM S;')
        SNO_list = cursor.fetchall()
        SNO_list = [i[0] for i in SNO_list]
    except Exception as e:
        if(str(e) == 'max() arg is an empty sequence'):
            print("\nS表中没有任何供应商信息\n")
        else:
            print("\n查询SNO失败！错误信息："+str(e)+"\n")
        db.close()
        return
    print('''
    请输入要删除的供应商：
    例如：S1
    目前供应商有：{}
    0. 返回
    '''.format("、".join(SNO_list)))
    print('> ', end='')
    SNO = input()
    if SNO == '0':
        db.close()
        return
    elif SNO in SNO_list:
        try:
            cursor.execute("DELETE FROM SPJ WHERE SNO = '{}';".format(SNO))
            cursor.execute("DELETE FROM S WHERE SNO = '{}';".format(SNO))
            db.commit()
            print("\n删除成功！\n")
        except Exception as e:
            db.rollback()
            print("\n删除失败！错误信息："+str(e)+"\n")
        db.close()
    else:
        print('输入错误！请重新输入！\n')
        db.close()
        delete()
        return

# 修改数据函数
def update():
    db = connect()
    cursor = db.cursor()
    try:
        cursor.execute('SELECT DISTINCT SNO FROM SPJ;')
        SNO_list = cursor.fetchall()
        SNO_list = [i[0] for i in SNO_list]
    except Exception as e:
        if(str(e) == 'max() arg is an empty sequence'):
            print("\nSPJ表中没有任何信息\n")
        else:
            print("\n查询SNO失败！错误信息："+str(e)+"\n")
        db.close()
        return
    print('''
    请输入要修改数据的供应商：
    例如：S1
    目前可修改的供应商有：{}
    0. 返回
    '''.format("、".join(SNO_list)))
    print('> ', end='')
    SNO = input()
    if SNO == '0':
        db.close()
        return
    elif SNO in SNO_list:
        print('''
    请选择要修改的数据：
    1. 修改至固定值
    2. 修改至相应倍率
    0. 返回
    ''')
        print('> ', end='')
        update_num = input()
        if update_num == '1':
            print('''
    请输入新的零件数值：
    例如：100
            ''')
            print('> ', end='')
            QTY = input()
            try:
                cursor.execute("UPDATE SPJ SET QTY = '{}' WHERE SNO = '{}';".format(QTY, SNO))
                db.commit()
                print("\n修改成功！\n")
            except Exception as e:
                db.rollback()
                print("\n修改失败！错误信息："+str(e)+"\n")
            db.close()
        elif update_num == '2':
            print('''
    请输入修改倍率：
    例如：1.1(提高10%)
            ''')
            print('> ', end='')
            NQTY = input()
            try:
                cursor.execute("UPDATE SPJ SET QTY = QTY * {} WHERE SNO = '{}';".format(NQTY, SNO))
                db.commit()
                print("\n修改成功！\n")
            except Exception as e:
                db.rollback()
                print("\n修改失败！错误信息："+str(e)+"\n")
            db.close()
        elif update_num == '0':
            db.close()
            return
        else:
            print('输入错误！请重新输入！\n')
            db.close()
            update()
            return
    else:
        print('输入错误！请重新输入！\n')
        db.close()
        update()
        return

# 查询数据函数
def watch():
    print('''
    请选择要查看的表：
    1. S
    2. P
    3. J
    4. SPJ
    0. 返回
    ''')
    print('> ', end='')
    table = input()
    if table == '1':
        table = 'S'
    elif table == '2':
        table = 'P'
    elif table == '3':
        table = 'J'
    elif table == '4':
        table = 'SPJ'
    elif table == '0':
        return
    else:
        print('输入错误！请重新输入！\n')
        watch()
        return
    db = connect()
    cursor = db.cursor()
    try:
        cursor.execute('SELECT * FROM {};'.format(table))
        data = cursor.fetchall()
        data = ["\t".join([str(j) for j in i]) for i in data]
        print('''
{}表中的数据为：
{}'''.format(table, "\n".join(data)))
    except Exception as e:
        print("\n查询失败！错误信息："+str(e)+"\n")
    db.close()


